<?php
/**
 * （国内）库存入库明细 （(国内)采购收货入库记录明细）
 * (model)
 */

namespace App\Models\Inventory;

use App\Models\Product\ProductClassify;
use App\Models\Product\ProductSku;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Query\Builder;
use Illuminate\Support\Facades\DB;

class StorageDetail extends Model
{
    protected $table = 'in_storage_detail';

    protected $guarded = [];

    public function storage(){
        return $this->belongsTo(Storage::class,'storage_no','storage_no');
    }

    public function sku(){
        return $this->belongsTo(ProductSku::class,'psku_id');
    }

    public function skuClassify(){
        return $this->hasOneThrough(
            ProductClassify::class,
            ProductSku::class,
            'id',//中间表
            'id',//目标表
            'psku_id',//当前模型
            'classify_id'//中间表
        );
    }

    /**
     * 入库记录汇总
     * @param $filters
     * @return Model|Builder|object|null
     */
    public static function getStorageDetailTotal($filters){
        $query = DB::table('in_storage_detail as sd')
            ->leftJoin('in_storage as s','sd.storage_no', '=', 's.storage_no')
            ->leftJoin('product_sku as ps','sd.psku_id','=','ps.id')
            ->leftJoin('product_classify as pc','ps.classify_id','=','pc.id')
            ->selectRaw('SUM(IFNULL(sd.storage_num, 0)) as storage_num,
                    SUM(IFNULL(sd.supplier_price, 0)) as supplier_price,
                    SUM(IFNULL(sd.storage_num, 0) * IFNULL(sd.supplier_price, 0)) as supplier_amount,
                    SUM(IFNULL(sd.unit_price,0)) as unit_price,
                    SUM(IFNULL(sd.unit_price_no_tax,0)) as unit_price_no_tax,
                    SUM(IFNULL(sd.pack_price,0)) as pack_price,
                    SUM(IFNULL(sd.storage_num, 0) * IFNULL(sd.unit_price, 0)) as amount');
        $query = self::formatFilters($query,$filters);
        return $query->first();
    }

    /**
     * 格式化过滤器
     * @param Builder $query
     * @param $filters
     * @return Builder
     */
    public static function formatFilters(Builder $query,$filters): Builder
    {
        foreach ($filters as $key => $_filter){
            if($key == 'sku8' && $_filter){
                $query->where(function ($query) use($_filter) {
                    $query->where('ps.psku_code', 'like', "%{$_filter}%")
                        ->orWhere('ps.psku_cn', 'like', "%{$_filter}%");
                });
            }
            if($key == 'sku' && isset($_filter['classify_id']) && $_filter['classify_id']){
                $query->where('ps.classify_id', $_filter['classify_id']);
            }
            if($key == 'skuClassify' && isset($_filter['season']) && $_filter['season']){
                $query->where('pc.season', $_filter['season']);
            }
            if($key == 'storage' && isset($_filter['supplier_id']) && $_filter['supplier_id']){
                $query->where('s.supplier_id', $_filter['supplier_id']);
            }
            if($key == 'created_at'){
                if(isset($_filter['start'])&&$_filter['start']){
                    $query->where('sd.created_at','>=',$_filter['start']);
                }
                if(isset($_filter['end'])&&$_filter['end']){
                    $query->where('sd.created_at','<=',$_filter['end']);
                }
            }
            if(in_array($key,['storage_no','order_no'])){
                if($_filter) {
                    $query->where('sd.'.$key,$_filter);
                }
            }
        }
        return $query;
    }
}
